package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.costelement.dao.CostElementDAO;
import com.cpi.cornpp.costelement.dto.CostElementDTO;
import com.cpi.cornpp.costelement.exception.CostElementException;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;

public class CostsElementsDao extends CostElementDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;
	
	/**SQL Select all cost elements*/
	protected static final String SQL_SELECT_ALL_COST_ELEMENTS = "SELECT cost_element_id, short_name, description, type_id FROM costs_elements";


	public CostsElementsDao() {
	}
	
	/**
	 * Get all the cost elements
	 * @return
	 * @throws CostElementException
	 */
	public ArrayList getArray(int orderField, boolean ascendingOrder, String filters)throws CostElementException{
		StringBuffer sql = null;
		CostElementDTO costElementDTO = null;
		ArrayList array	= null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			array = new ArrayList(20);
			sql = new StringBuffer(SQL_SELECT_ALL_COST_ELEMENTS);
			
			if (filters.length() > 0){
				sql.append(SQL_WHERE);
				sql.append(filters);
			}
			
			sql.append(SQL_ORDER_BY);
			switch (orderField){
				case 1: sql.append(CostElementDTO.SHORT_NAME);
						break;
				case 2: sql.append(CostElementDTO.DESCRIPTION);
						break;
			}
			
			if (ascendingOrder){
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
			
			pstm = prepareSQL(sql.toString(), dsName);
			rs = executeQuery(pstm);
			
			while (rs.next()){
				costElementDTO = new CostElementDTO();
				costElementDTO.setCostElementID(rs.getInt(1));
				costElementDTO.setCostElementCode(rs.getString(2));
				costElementDTO.setCostElementDescription(rs.getString(3));
				costElementDTO.setTypeId(rs.getInt(4));
				array.add(costElementDTO);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			CostElementException costElementException = null;
			poolException.printStackTrace(System.out);
			costElementException = new CostElementException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		} catch(SQLException sqle){
			CostElementException costElementException = null;
			sqle.printStackTrace(System.out);
			costElementException = new CostElementException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw costElementException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}	
		
		return array;		
	}

}
